﻿

import os
import pymysql

path = "{0}/text".format(os.getcwd())

def listPath(path: str) -> list:
    fileList = list()
    tuples = os.listdir(path)
    for tuple in tuples:
        currentPath = "{0}/{1}".format(path, tuple)
        fileList.append(currentPath)
    return fileList


# 打开数据库连接
db = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='db_game',
    charset='utf8'
)
cursor = db.cursor()

# 不是诗人信息再插入
# if(path.split("/")[-1].split(".")[0] != "meta"):
def insertPoetToMysql(data: tuple[str]) -> int:
    # 插入数据库
    sql = """insert into tb_poet values(0, "%s", "%s", "%s")"""
    # data = [
    #         ('张书豪1', '男', '18536475134'),
    #         ('张三1', '男', '18536575134'),
    #         ('貂蝉1', '女', '18436575134'),
    #         ('小乔1', '女', '13436575134')
    #     ]
    try:
        cursor.execute(sql, data)
        last_id = cursor.lastrowid
        db.commit()
        return last_id
    except Exception as e:
        print("insertPoetToMysql 异常:{0}".format(e)) 
        db.rollback()
        return -1

# 插入诗歌表
def insertPoemToMysql(data: list[tuple[str]]) -> None:
    sql = """insert into tb_poem values(0, %s, %s, %s)"""
    try:
        cursor.executemany(sql, data)
        db.commit()
    except Exception as e: 
        print("insertPoemToMysql 异常:{0}".format(e)) 
        db.rollback()


# 读取诗词
def readPoem(path: str) -> str:
    poemList = list[str]()
    with open(path, "r", encoding="UTF-8") as f:
        for line in f:
            if("=" not in line and "\n" != line):
                poemList.append(line.strip())
    return "\n".join(poemList)


if __name__ == "__main__":
    list1s = listPath(path)
    for list1 in list1s:
        # 朝代
        dynasty = list1.split(".")[-1]
        list2s = listPath(list1)
        for list2 in list2s:
            # 诗人
            poet = list2.split("/")[-1]
            # print(poet)
            # print(list2)
            
            # 诗人info
            poet_info = readPoem("{0}/meta.txt".format(list2))
            # print(poet_info)
            # 插入到tb_poem表，返回poet_id
            last_id = insertPoetToMysql(data=(poet, dynasty, poet_info))
            list3s:list[str] = listPath(list2)
            poemList = list[tuple[str]]()
            for list3 in list3s:
                # 诗词
                poemName = list3.replace("\\", "/").split("/")[-1].split(".")[0]
                if(poemName != "meta"):
                    poemContent = readPoem(list3)
                    poemList.append((poemName, poemContent, last_id))
            insertPoemToMysql(poemList)
    # 关闭数据库
    cursor.close()
    db.close()



        

# f = open(file="text/01.先秦/屈原/离骚.txt", mode="r", encoding="UTF-8")
# for line in f.readlines():
#     if("=" not in line and not line.__eq__("\n")):

#         text = line.strip()
#         print(text)
# f.close()



"""sumary_line
TODO 联表插入
数据库连接
Keyword arguments:
argument -- description
Return: return_description
"""
# 打开数据库连接
# db = pymysql.connect(
#     host='localhost',
#     user='root',
#     password='123456',
#     database='db_person',
#     charset='utf8'
# )
# cursor = db.cursor()
# sql = """insert into tb_student values(0, "貂蝉", "女", "18436575134")"""
# try:
#     cursor.execute(sql)
#     db.commit()
# except: 
#     db.rollback()

# cursor.execute("""select * from tb_student""")
# data = cursor.fetchall()
# for column in data:
#     print(column)
# db.close()


# 路径：C:\Users\shuhao1.zhang\Downloads\shici-master\shici-master\web\src\main\resources\text